/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import connection.ConnectionDB;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Hotels;
import model.Locations;
import model.PackageTour;
import model.PackageTourDetails;
import model.PackageType;
import model.Places;
import model.TourBooking;

/**
 *
 * @author Nhang Con
 */
public class TourDAL {

    private ConnectionDB conn = new ConnectionDB();
    private CallableStatement cs;
    private ResultSet rs;

    public boolean createTourBooking(int customerID, int packageTourID, int quantity, float totalPrice) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_CreateTourBooking(?,?,?,?)}");
            cs.setInt("CustomerID", customerID);
            cs.setInt("PackageTourID", packageTourID);
            cs.setInt("Quantity", quantity);
            cs.setFloat("TotalPrice", totalPrice);
            if (cs.executeUpdate() == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean newPackageTour(PackageTour ptour) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_NewTour(?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setString("des", ptour.getDescription());
            cs.setInt("packagetype", ptour.getPackageTypeID());
            cs.setString("packageTourName", ptour.getPackageTourName());
            cs.setInt("hotelid", ptour.getHotelID());
            cs.setInt("placeid", ptour.getPlaceID());
            cs.setInt("locationid", ptour.getLocationID());
            cs.setFloat("price", ptour.getPrice());
            cs.setBoolean("available", ptour.isAvailable());
            cs.setString("fromdate", ptour.getFromdate());
            cs.setString("todate", ptour.getEnddate());
            cs.setString("image", ptour.getImages());
            cs.setString("travel", ptour.getTravel());

            if (cs.executeUpdate() == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public List<PackageTour> listAllPackageTour() {
        List<PackageTour> list = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {
            cs = conn.createConnection().prepareCall("{call sp_ListPackageTour()}");
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));
                pkTour.setFromdate(rs.getString("FromDate"));
                pkTour.setEnddate(rs.getString("ToDate"));

                pkTour.setAvailable(rs.getBoolean("Available"));
                list.add(pkTour);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public PackageTour objectPackageTour(int idtour) {
        PackageTour pkTour = null;

        try {
            cs = conn.createConnection().prepareCall("{call sp_objectPktour(?)}");
            cs.setInt("tourid", idtour);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));
//                pkTour.setFromdate(rs.getString("FromDate"));
//                pkTour.setEnddate(rs.getString("ToDate"));
//                DateFormat fmat = new SimpleDateFormat("dd-MM-yyyy");
//                Date fdate = rs.getDate("FromDate");
//                fmat.format(fdate);
                pkTour.setFdate(rs.getDate("FromDate"));

//                Date edate = rs.getDate("ToDate");
//                fmat.format(edate);
                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));


            }
            return pkTour;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pkTour;
    }

    public PackageTourDetails getPackageTourDetailsByID(int tourID) {
        PackageTourDetails ptDetails = null;
        try {
            cs = conn.createConnection().prepareCall("{call sp_GetDetailsPackageTourByID(?)}");
            cs.setInt("TourID", tourID);
            rs = cs.executeQuery();
            while (rs.next()) {
                ptDetails = new PackageTourDetails();
                ptDetails.setPackageTourID(tourID);
                ptDetails.setPackageTourName(rs.getString("PackageTourName"));
                ptDetails.setPrice(rs.getFloat("Price"));
                ptDetails.setTravel(rs.getString("Travel"));
                ptDetails.setDescription(rs.getString("Description"));
                ptDetails.setImages(rs.getString("Images"));
                ptDetails.setFromDate(rs.getString("FromDate"));
                ptDetails.setToDate(rs.getString("ToDate"));
                ptDetails.setPackageName(rs.getString("PackageName"));
                ptDetails.setHotelName(rs.getString("HotelName"));
                ptDetails.setHotelRate(rs.getInt("HotelRate"));
                ptDetails.setLocationName(rs.getString("LocationName"));
                ptDetails.setPlaceName(rs.getString("PlaceName"));
            }

            return ptDetails;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ptDetails;
    }

    public List<Hotels> listHotel() {
        List<Hotels> lt = new ArrayList<Hotels>();

        try {
            cs = conn.createConnection().prepareCall("{call sp_ListHotel()}");
            rs = cs.executeQuery();
            while (rs.next()) {
                Hotels ht = new Hotels();
                ht.setHotelID(rs.getInt("HotelID"));
                ht.setHotelName(rs.getString("HotelName"));
                ht.setPlaceID(rs.getInt("PlaceID"));
                ht.setPrice(rs.getFloat("HotelPrice"));
                ht.setRate(rs.getInt("HotelRate"));
                lt.add(ht);

            }
            return lt;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lt;
    }

    public List<Places> listPlace() {
        List<Places> pt = new ArrayList<Places>();

        try {
            cs = conn.createConnection().prepareCall("{call sp_ListPlace()}");
            rs = cs.executeQuery();
            while (rs.next()) {
                Places places = new Places();
                places.setPlaceID(rs.getInt("PlaceID"));
                places.setPlaceName(rs.getString("PlaceName"));
                places.setLocationID(rs.getInt("LocationID"));
                places.setDescription(rs.getString("Description"));

                pt.add(places);

            }
            return pt;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pt;
    }

    public List<Locations> listLocation() {
        List<Locations> lt = new ArrayList<Locations>();

        try {

            cs = conn.createConnection().prepareCall("{call sp_ListLocation()}");
            rs = cs.executeQuery();
            while (rs.next()) {
                Locations lcation = new Locations();
                lcation.setLocationID(rs.getInt("LocationID"));
                lcation.setLocationName(rs.getString("LocationName"));
                lcation.setDescription(rs.getString("Description"));
                lt.add(lcation);

            }
            return lt;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }

        return lt;
    }

    public List<PackageType> listPackageType() {
        List<PackageType> lt = new ArrayList<PackageType>();

        try {

            cs = conn.createConnection().prepareCall("{call sp_ListAllPackageType()}");
            rs = cs.executeQuery();
            while (rs.next()) {
                PackageType lpt = new PackageType();
                lpt.setPackageTypeID(rs.getInt("PackageTypeID"));
                lpt.setPackageTypeName(rs.getString("PackageName"));
                lpt.setDescription(rs.getString("Description"));
                lt.add(lpt);

            }
            return lt;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }

        return lt;
    }

    public List<Hotels> listHotelByPlaceID(int placeID) {
        List<Hotels> list = new ArrayList<Hotels>();

        try {

            cs = conn.createConnection().prepareCall("{call sp_ListHotelByPlaceID (?)}");
            cs.setInt("placeID", placeID);
            rs = cs.executeQuery();
            while (rs.next()) {
                Hotels hotels = new Hotels();
                hotels.setHotelID(rs.getInt("HotelID"));
                hotels.setHotelName(rs.getString("HotelName"));
                hotels.setPlaceID(rs.getInt("PlaceID"));
                hotels.setPrice(rs.getFloat("HotelPrice"));
                hotels.setRate(rs.getInt("HotelRate"));
                list.add(hotels);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public boolean updatePTour(PackageTour pktour) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_UpdateTour(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt("tourid", pktour.getPackageTourID());
            cs.setInt("hotelid", pktour.getHotelID());
            cs.setInt("locationid", pktour.getLocationID());
            cs.setInt("placeid", pktour.getPlaceID());
            cs.setInt("packagetype", pktour.getPackageTypeID());
            cs.setString("des", pktour.getDescription());
            cs.setString("packageTourName", pktour.getPackageTourName());
            cs.setString("travel", pktour.getTravel());
            Date fdate = pktour.getFdate();
            SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
            String fdateString = formatter.format(fdate);
            cs.setString("fromdate", fdateString);
            Date edate = pktour.getEdate();
            String edateString = formatter.format(edate);
            cs.setString("todate", edateString);
            cs.setString("image", pktour.getImages());
            cs.setFloat("price", pktour.getPrice());
            cs.setBoolean("available", pktour.isAvailable());
            if (cs.executeUpdate() == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }



    }

    public int getRowInPKT() {
        try {
            int rowIntable = 0;
            cs = conn.createConnection().prepareCall("{call sp_getRowInPackageTour()}");
            rs = cs.executeQuery();
            if (rs.next()) {
                rowIntable = rs.getInt("rows");
                return rowIntable;
            }
            return 0;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }

    public List<PackageTour> listPKTByPage(int page) {
        List<PackageTour> lpackage = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {
            cs = conn.createConnection().prepareCall("{call sp_ListPKTByPage(?)}");
            cs.setInt("indexpage", page);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));
                pkTour.setFdate(rs.getDate("FromDate"));
                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));

                lpackage.add(pkTour);
            }
            return lpackage;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }

    public List<PackageTour> searchPackageTourByName(String tourName) {
        List<PackageTour> list = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {

            cs = conn.createConnection().prepareCall("{call sp_SearchByTourName(?)}");
            cs.setString("tourname", tourName);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));

                pkTour.setFdate(rs.getDate("FromDate"));

                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));


                list.add(pkTour);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }

    public List<PackageTour> ListTourByType(int typeid) {
        List<PackageTour> list = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {

            cs = conn.createConnection().prepareCall("{call sp_listPKTByType(?)}");
            cs.setInt("typeid", typeid);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));

                pkTour.setFdate(rs.getDate("FromDate"));

                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));


                list.add(pkTour);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<PackageTour> listPackageTourByTypeID(int typeID) {
        List<PackageTour> listPackageTour = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {
            cs = conn.createConnection().prepareCall("{call sp_ListPackageTourByPackageType(?)}");
            cs.setInt("typeID", typeID);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));
                pkTour.setFdate(rs.getDate("FromDate"));
                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));

                listPackageTour.add(pkTour);
            }
            return listPackageTour;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<PackageTour> searchPackageTourByPrice(float priceFrom, float priceTo) {
        List<PackageTour> list = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {
            cs = conn.createConnection().prepareCall("{call sp_SearchTourByPrice(?,?)}");
            cs.setFloat("pricefrom", priceFrom);
            cs.setFloat("priceto", priceTo);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));
                pkTour.setFdate(rs.getDate("FromDate"));
                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));
                list.add(pkTour);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public TourBooking getTourBookingByID(int tourBookingID) {
        TourBooking tourBooking = null;
        try {
            cs = conn.createConnection().prepareCall("{call sp_GetTourBookingByID(?)}");
            cs.setInt("TourBookingID", tourBookingID);
            rs = cs.executeQuery();
            while (rs.next()) {
                tourBooking = new TourBooking();
                tourBooking.setTourBookingID(tourBookingID);
                tourBooking.setCustomerID(rs.getInt("CustomerID"));
                tourBooking.setPackageTourID(rs.getInt("PackageTourID"));
                tourBooking.setQuantity(rs.getInt("Quantity"));
                tourBooking.setPrice(rs.getFloat("TotalPrice"));
                tourBooking.setAvailable(rs.getBoolean("Available"));
            }

            return tourBooking;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return tourBooking;
    }

    public List<PackageTour> listTourByState(int state) {
        List<PackageTour> listPackageTour = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {
            cs = conn.createConnection().prepareCall("{call sp_SearchTourByState(?)}");
            cs.setInt("state", state);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));
                pkTour.setFdate(rs.getDate("FromDate"));
                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));

                listPackageTour.add(pkTour);
            }
            return listPackageTour;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<PackageTour> advanceSearchPackageTour(String localName,float priceFrom, float priceTo) {
        List<PackageTour> list = new ArrayList<PackageTour>();
        PackageTour pkTour;
        try {
            cs = conn.createConnection().prepareCall("{call sp_advanceSearchTour(?,?,?)}");
            cs.setString("locationname",localName);
            cs.setFloat("pricefrom", priceFrom);
            cs.setFloat("priceto", priceTo);
            rs = cs.executeQuery();
            while (rs.next()) {
                pkTour = new PackageTour();
                pkTour.setPackageTourID(rs.getInt("PackageTourID"));
                pkTour.setPackageTourName(rs.getString("PackageTourName"));
                pkTour.setPackageTypeID(rs.getInt("PackageTypeID"));
                pkTour.setHotelID(rs.getInt("HotelID"));
                pkTour.setLocationID(rs.getInt("LocationID"));
                pkTour.setPlaceID(rs.getInt("PlaceID"));
                pkTour.setPrice(rs.getFloat("Price"));
                pkTour.setTravel(rs.getString("Travel"));
                pkTour.setDescription(rs.getString("Description"));
                pkTour.setImages(rs.getString("Images"));
                pkTour.setFdate(rs.getDate("FromDate"));
                pkTour.setEdate(rs.getDate("ToDate"));
                pkTour.setAvailable(rs.getBoolean("Available"));
                list.add(pkTour);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(TourDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
}
